/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package beans;

import database.Database;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
/**
 *
 * @author Dhruv
 */

//this javabeans class represents a customer
public class SeekerCustomer {

    //customer's name, password and profile picture
    private String name, password, profilePicture;
    //what image the customer is currently viewing
    private SeekerUploadImg viewingImage;

    //constructor
    public SeekerCustomer(String name, String password, String profilePicture) {
        this.name = name;
        //password has no getter method,
        //the only way to set it is by creating new user
        this.password = password;
        this.profilePicture = profilePicture;
    }

    //empty constructor, needed to create concrete Action subclasses
    public SeekerCustomer(){}

    //get customer's name
    public String getName() {
        //name never changes for a customer, so no need to query database
        return this.name;
    }

    //get customer's picture
    public String getPicture() {
        Connection conn = Database.getConnection();
        String query = "SELECT picture FROM customer WHERE name = '"+ this.getName() + "'";
        String pic = "";
        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                pic = rs.getString("picture");
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return pic;
    }

    //insert new customer into database
    public void setCustomer(String name, String password, String picture) {        
        Connection conn = Database.getConnection();
        String query = "INSERT INTO customer (name, password, picture) VALUES ('"
                +name+"','"+password+"','"+picture+"')";
        try {
            //execute query
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            conn.commit();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        
    }
    
    //retrieve customer from database
    //customer might be existing customer or new customer
    public static SeekerCustomer getCustomer(String name) {
        Connection conn = Database.getConnection();
        String query = "SELECT name, password, picture FROM customer WHERE name = '"+ name + "'";
        String [] param = new String [3];
        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                param[0] = rs.getString("name");
                param[1] = rs.getString("password");
                param[2] = rs.getString("picture");
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //if there are no records of the customer, return null object
        if(param[0] == null || param[1] == null)
            return null;
        //else create and return a customer object
        else
            return new SeekerCustomer(param[0], param[1], param[2]);
    }

    //added but not accepted yet
    public ArrayList<String> getAddedFriends() {
        Connection conn = Database.getConnection();
        String query = "SELECT friend FROM relationship WHERE name='"
                +this.getName()+"' AND mutualFriend='N'";
        ArrayList<String> addedFriends = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                addedFriends.add(rs.getString("friend"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return addedFriends;
    }

    //get all customers except for this user, his mutual friends,
    //people whom he has sent friend requests to (which havent been
    //accepted yet) and people who sent this user a request
    public ArrayList<SeekerCustomer> getAllCustomers() {
        Connection conn = Database.getConnection();
        //construct an initial string, to be expanded later
        String query = "SELECT name, password, picture FROM customer WHERE NOT name='"+this.getName()+"' ";
        //temporary variables
        ArrayList<String> temp1 = new ArrayList<String>();
        ArrayList<String> temp2 = new ArrayList<String>();
        ArrayList<String> temp3 = new ArrayList<String>();
        ArrayList<String> friendRequestsName = new ArrayList<String>();
        //whom you added but has yet to accept request
        ArrayList<String> addedFriends = this.getAddedFriends();
        //whose already your friend
        ArrayList<String> mutualFriends = this.getMutualFriends();
        //who added you
        ArrayList<SeekerFriend> friendRequests = this.getFriendRequests();
        //combines all the above mentioned arrays
        ArrayList<String> total = new ArrayList<String>(addedFriends);

        //get the friend names from the friend objects
        for(int i=0; i<friendRequests.size(); i++){
            friendRequestsName.add(friendRequests.get(i).getFriend());
        }        

        //combine the various arrays
        total.addAll(mutualFriends);
        total.addAll(friendRequestsName);

        //construct the query string
        for(int i=0; i<total.size(); i++)
        {
            query= query +"AND NOT name='"+total.get(i)+"' ";
        }

        //array list which willl be returned by this method
        ArrayList<SeekerCustomer> allCustomers = new ArrayList<SeekerCustomer>();
        
        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp1.add(rs.getString("name"));
                temp2.add(rs.getString("password"));
                temp3.add(rs.getString("picture"));

            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //construct the arraylist of SeekerCustomers
        for(int i=0; i<temp1.size(); i++)
        {
            allCustomers.add(new SeekerCustomer(temp1.get(i), temp2.get(i), temp3.get(i)));
        }

        return allCustomers;
    }

    //get profile picture of corresponding customers
    public ArrayList<String> getCorrespondingPics(ArrayList<String> names){
        Connection conn = Database.getConnection();
        String query = "SELECT picture FROM customer WHERE name=''";

        //expand the query string with all the customer names
        for(int i=0; i<names.size(); i++){
            query = query + " OR name='"+names.get(i)+"'";
        }

        //arraylist to be returned
        ArrayList<String> correspondingPics = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                correspondingPics.add(rs.getString("picture"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return correspondingPics;
    }

    //get the exact time this user recieved the various friend requests
    public ArrayList<String> getCorrespondingDateTime(ArrayList<String> names){
        Connection conn = Database.getConnection();
        String query = "SELECT datetime FROM relationship WHERE name=''";

        //expand the query string with all the customer names
        for(int i=0; i<names.size(); i++){
            query = query + " OR name='"+names.get(i)+"'";
        }

        //arraylist to be returned
        ArrayList<String> correspondingDatetime = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                correspondingDatetime.add(rs.getString("datetime"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return correspondingDatetime;
    }
    
    //adds friend to database; the other friend still has to accept/decline
    //request in order to be/not be a mutual friend
    public void setFriend(String addedFriend) {

        Connection conn = Database.getConnection();
        //make the mutualFriend flag 'N' until the other friend accepts (it'll become 'Y')
        String query = "INSERT INTO relationship (name, friend, mutualFriend, datetime) VALUES ('"
                +this.getName()+"','"+addedFriend+"','"+"N"+"','"+getTime()+"')";

        try {
            //execute query
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            conn.commit();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    //get all friend requests to this user
    public ArrayList<SeekerFriend> getFriendRequests() {
        Connection conn = Database.getConnection();
        //match the query with the mutualFriend flag set to 'N'
        //which indicates that the request has not been accepted yet
        String query = "SELECT name FROM relationship WHERE friend='"
                +this.getName()+"' AND mutualFriend='N'";

        //temporary variable
        ArrayList<String> friendReq = new ArrayList<String>();
        //will be returned by this method
        ArrayList<SeekerFriend> friendObj = new ArrayList<SeekerFriend>();
        
        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                friendReq.add(rs.getString("name"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately 
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //get the profile picture of the requestee's
        ArrayList<String> corrPic = this.getCorrespondingPics(friendReq);
        //get the exact time this user recieved the various friend requests
        ArrayList<String> corrDT = this.getCorrespondingDateTime(friendReq);

        //construct the arraylist to be returned
        for(int i =0; i< friendReq.size(); i++){
            friendObj.add(new SeekerFriend(friendReq.get(i), corrPic.get(i), convertTime(corrDT.get(i))));
        }

        return friendObj;
    }

    //set the friend as a mutual friend
    public void setMutualFriend(String mutFriend) {
        Connection conn = Database.getConnection();

        String[] queries = new String[2];
        String timeOfFriendship = getTime();

        //we need to first update the current user-friend mapping, changing mutualFriend flag to 'Y'
        queries[0] = "UPDATE relationship SET mutualFriend='Y', datetime='"+timeOfFriendship+"' WHERE name='"
                +mutFriend+"' AND friend='"+this.getName()+"'";
        //next, we need to create a new friend-user mapping, we also add time of friendship
        queries[1] = "INSERT INTO relationship (name, friend, mutualFriend, datetime) VALUES ('"
                +this.getName()+"','"+mutFriend+"','Y','"+timeOfFriendship+"')";

        try {
            //execute queries as a batch
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            for(int i=0; i<queries.length; i++){
                stmt.addBatch(queries[i]);
            }
            int [] updateCounts = stmt.executeBatch();
            conn.commit();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }  
    }

    //returns all mutual friends according to the time they became friends
    //in descending order
    public ArrayList<String> getMutualFriends() {
        
        Connection conn = Database.getConnection();
        //ORDER BY DESC returns the descending order
        String query = "SELECT friend FROM relationship WHERE name='"
                +this.getName()+"' AND mutualFriend='Y' ORDER BY datetime DESC";

        ArrayList<String> mutualFriends = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                mutualFriends.add(rs.getString("friend"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return mutualFriends;
    }

    //remove the user-friend request mapping from the database
    //this makes it possible for this user to add the declined friend later
    public void setDeclineFriend(String declineFriend) {
        Connection conn = Database.getConnection();
        //delete the corresponding row
        String query = "DELETE FROM relationship WHERE name='"
                +declineFriend+"' AND friend='"+this.getName()+"' AND mutualFriend='N'";

        try {
            //execute query
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            conn.commit();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    //converts the epoch time to human readable time
    public String convertTime(String time) {
        Long l = Long.parseLong(time);
        return new java.text.SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new java.util.Date (l));

    }

    //returns the current time in a string
    public String getTime() {
        Calendar now = Calendar.getInstance();
        return new Long(now.getTimeInMillis()).toString();
    }

    //retrieve image object from database
    public SeekerUploadImg getImage() {
        Connection conn = Database.getConnection();
        //retrieve the latest image metadata
        //time inserted determines which is the latest; only take the top result
        String query = "SELECT * FROM resource WHERE name='"+this.getName()+
                "' ORDER BY datetime DESC LIMIT 1";

        //temporary variable
        ArrayList<String> temp = new ArrayList<String>();
        
        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp.add(rs.getString("name"));
                temp.add(rs.getString("image"));
                temp.add(rs.getString("datetime"));
            }
            
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        //create the object to be returned
        SeekerUploadImg lastAddedImg = new SeekerUploadImg(temp.get(0), temp.get(1), convertTime(temp.get(2)));

        return lastAddedImg;
    }

    //add image into database
    public void setImage(String image) {
        Connection conn = Database.getConnection();
        //insert the image string, and also add time of upload
        //this way user can upload many images with the same name
        String query ="INSERT INTO resource (name, image, datetime) VALUES ('"
                +this.getName()+"','"+image+"','"+getTime()+"')";

        try {
            //execute query
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            conn.commit();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }    

    //retrieve this users image objects from database
    public ArrayList<SeekerUploadImg> getMyImages() {
        Connection conn = Database.getConnection();
        String query = "SELECT * FROM resource WHERE name='"
                +this.getName()+"'";

        //temporary variables
        ArrayList<String> temp1 = new ArrayList<String>();
        ArrayList<String> temp2 = new ArrayList<String>();
        ArrayList<String> temp3 = new ArrayList<String>();

        //to be returned in this method
        ArrayList<SeekerUploadImg> myImg = new ArrayList<SeekerUploadImg>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp1.add(rs.getString("name"));
                temp2.add(rs.getString("image"));
                temp3.add(rs.getString("datetime"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //construct the arraylist
        for(int i=0; i<temp3.size(); i++)
        {
            myImg.add(new SeekerUploadImg(temp1.get(i), temp2.get(i), convertTime(temp3.get(i))));
        }

        return myImg;
    }

    //what image is being viewed
    public void setViewingImage(String uploader, String uploadedImg, String uploaderDT) {
        this.viewingImage = new SeekerUploadImg(uploader, uploadedImg, uploaderDT);
    }

    //return what image is being viewed
    public SeekerUploadImg getViewingImage() {
        return this.viewingImage;
    }

    //sets the tag on the current image being viewed
    public void setMyTag(String myTag) {
        Connection conn = Database.getConnection();
        //uses current image being viewed and also adds tag name, who tagged it
        //and what time it was tagged; tagger could either be the uploader or
        //the uploaders mutual friend
        String query ="INSERT INTO tags (name, image, imgdatetime, tagger, tag, tagdatetime) VALUES ('"
            +this.getViewingImage().getName()+"','"+this.getViewingImage().getImage()+"','"
            +this.getViewingImage().getDatetime()+"','"+this.getName()+"','"+myTag+"','"+getTime()+"')";

        try {
            //execute query
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            conn.commit();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }        
    }

    //get the tag object
    public SeekerImgTag getMyTag(String thisTag) {
        Connection conn = Database.getConnection();
        //there might be many images with this tag, which is why we
        //need to identify the exact image and time it was uploaded
        String query = "SELECT * FROM tags WHERE name='"
                +this.getViewingImage().getName()+"' AND image='"+this.getViewingImage().getImage()
                +"' AND imgdatetime='"+this.getViewingImage().getDatetime()+"' AND tag='"+thisTag+"'";

        //temporary variable
        ArrayList<String> temp = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp.add(rs.getString("name"));
                temp.add(rs.getString("image"));
                temp.add(rs.getString("imgdatetime"));
                temp.add(rs.getString("tagger"));
                temp.add(rs.getString("tag"));
                temp.add(rs.getString("tagdatetime"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //construct the tagobject using returned values
        SeekerUploadImg up = new SeekerUploadImg(temp.get(0), temp.get(1), temp.get(2));
        SeekerImgTag myTag = new SeekerImgTag(up, temp.get(3), temp.get(4), convertTime(temp.get(5)));

        return myTag;
    }

    //retrieve this users tags from database
    public ArrayList<String> getMyTags() {
        Connection conn = Database.getConnection();
        //DISTINCT keyword used because there might be many images with
        //the same tag
        String query = "SELECT DISTINCT tag FROM tags WHERE name='"
                +this.getName()+"'";

        //to be returned by this method
        ArrayList<String> myTag = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                myTag.add(rs.getString("tag"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return myTag;
    }

    //construct an arraylist of tag objects according to the tag name
    public ArrayList<SeekerImgTag> getTagObjects(String viewTag) {
        
        Connection conn = Database.getConnection();
        String query = "SELECT * FROM tags WHERE tag='"
                +viewTag+"'";

        //temporary variables
        ArrayList<String> temp1 = new ArrayList<String>();
        ArrayList<String> temp2 = new ArrayList<String>();
        ArrayList<String> temp3 = new ArrayList<String>();
        ArrayList<String> temp4 = new ArrayList<String>();
        ArrayList<String> temp5 = new ArrayList<String>();
        ArrayList<String> temp6 = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp1.add(rs.getString("name"));
                temp2.add(rs.getString("image"));
                temp3.add(rs.getString("imgdatetime"));
                temp4.add(rs.getString("tagger"));
                temp5.add(rs.getString("tag"));
                temp6.add(rs.getString("tagdatetime"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //to be returned by method
        ArrayList<SeekerImgTag> tagObjs = new ArrayList<SeekerImgTag>();

        //construct the arraylist using temp variables
        for(int i=0; i<temp1.size(); i++)
        {
            tagObjs.add(new SeekerImgTag(new SeekerUploadImg(temp1.get(i),temp2.get(i),temp3.get(i)),
                    temp4.get(i), temp5.get(i), convertTime(temp6.get(i))));
        }
        
        return tagObjs;
    }

    //checks if image viewer and image uploader are mutual friends
    public boolean isMutualFriend(String viewer, String uploader) {
        Connection conn = Database.getConnection();
        //mutualFriend is a flag value specifying the type of friendship
        String query = "SELECT mutualFriend FROM relationship WHERE name='"
                +viewer+"' AND friend='"+uploader+"'";
        //temporarary variable
        String temp = "";
        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp= rs.getString("mutualFriend");
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //if the flag value is 'Y' or if viewer is viewing
        //his own image, return true
        if(temp.equals("Y") || viewer.equals(uploader))
            return true;
        //else return false
        else
            return false;
    }

    //gives an ordered list of all tags by popularity
    public ArrayList<String> getToptags() {
        Connection conn = Database.getConnection();
        //count the number of times a given tag appears in database 
        //and order by descending popularity
        String query = "SELECT tag,COUNT(*) as count FROM tags GROUP BY tag ORDER BY count DESC";

        //to be returned by this method
        ArrayList<String> topTags = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                topTags.add(rs.getString("tag"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return topTags;
    }

    //get all tags associated with an current viewing image
    public ArrayList<String> getAssociatedTags() {
        Connection conn = Database.getConnection();
        //use the specific image which is being viewed
        String query = "SELECT tag FROM tags WHERE name='"+this.viewingImage.getName()+
                "' AND image='"+this.viewingImage.getImage()+"' AND "
                + "imgdatetime='"+this.viewingImage.getDatetime()+"'";

        //to be returned in this method
        ArrayList<String> associatedTags = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                associatedTags.add(rs.getString("tag"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return associatedTags;
    }

    //get all the images the friends added AFTER they became mutual friends
    public ArrayList<SeekerUploadImg> getAllFriendsImages() {
        Connection conn = Database.getConnection();
        //returns all mutual friends according to the time they became friends
        //in descending order
        ArrayList<String> mutFriends = this.getMutualFriends();
        //returns the exact time mutual friends were made
        //ordered by the time they became friends in descending order
        ArrayList<String> mutFriendsTime = this.getMutualFriendsTime();
        //to be returned by this method
        ArrayList<SeekerUploadImg> allFrImg = new ArrayList<SeekerUploadImg>();
        //temporary variables
        ArrayList<String> temp1 = new ArrayList<String>();
        ArrayList<String> temp2 = new ArrayList<String>();
        ArrayList<String> temp3 = new ArrayList<String>();
        
        //intial query string
        String query = "SELECT name, image, datetime FROM resource WHERE name=''"
                + " AND datetime>''";

        //expand query string
        for(int i=0; i<mutFriends.size(); i++)
        {
            query = query + " OR name='"+mutFriends.get(i)+"' AND datetime>'"
                    +mutFriendsTime.get(i)+"'";
        }

        //further expand query string; in descending order by time
        query = query + " ORDER BY datetime DESC";

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp1.add(rs.getString("name"));
                temp2.add(rs.getString("image"));
                temp3.add(rs.getString("datetime"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //construct the arraylist to be returned
        for(int i=0; i<temp1.size(); i++)
        {
            allFrImg.add(new SeekerUploadImg(temp1.get(i), temp2.get(i), convertTime(temp3.get(i))));
        }

        return allFrImg;
    }

    //returns the exact time mutual friends were made
    //ordered by the time they became friends in descending order
    public ArrayList<String> getMutualFriendsTime() {

        Connection conn = Database.getConnection();
        //ORDER BY and DESC ensure descending order
        String query = "SELECT datetime FROM relationship WHERE name='"
                +this.getName()+"' AND mutualFriend='Y' ORDER BY datetime DESC";

        //to be returned by this method
        ArrayList<String> mutFrTime = new ArrayList<String>();

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                mutFrTime.add(rs.getString("datetime"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return mutFrTime;
    }

    //get all the tags the friends added AFTER they became mutual friends
    public ArrayList<SeekerImgTag> getAllFriendsTags() {
        Connection conn = Database.getConnection();
        //returns all mutual friends according to the time they became friends
        //in descending order
        ArrayList<String> mutFriends = this.getMutualFriends();
        //returns the exact time mutual friends were made
        //ordered by the time they became friends in descending order
        ArrayList<String> mutFriendsTime = this.getMutualFriendsTime();
        //to be returned by this method
        ArrayList<SeekerImgTag> allFrTag = new ArrayList<SeekerImgTag>();
        //temporary variables
        ArrayList<String> temp1 = new ArrayList<String>();
        ArrayList<String> temp2 = new ArrayList<String>();
        ArrayList<String> temp3 = new ArrayList<String>();
        ArrayList<String> temp4 = new ArrayList<String>();
        ArrayList<String> temp5 = new ArrayList<String>();
        ArrayList<String> temp6 = new ArrayList<String>();

        //intial query string
        String query = "SELECT name, image, imgdatetime, tagger, tag, tagdatetime"
                + " FROM tags WHERE tagger='' AND tagdatetime>''";

        //expand query string
        for(int i=0; i<mutFriends.size(); i++)
        {
            query = query + " OR tagger='"+mutFriends.get(i)+"' AND tagdatetime>'"
                    +mutFriendsTime.get(i)+"'";
        }

        //further expand query string; results in descending order
        query = query + " ORDER BY tagdatetime DESC";

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                temp1.add(rs.getString("name"));
                temp2.add(rs.getString("image"));
                temp3.add(rs.getString("imgdatetime"));
                temp4.add(rs.getString("tagger"));
                temp5.add(rs.getString("tag"));
                temp6.add(rs.getString("tagdatetime"));
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        //construct the arraylist to be returned
        for(int i=0; i<temp1.size(); i++)
        {
            allFrTag.add(new SeekerImgTag(new SeekerUploadImg(temp1.get(i), temp2.get(i),
                    temp3.get(i)), temp4.get(i), temp5.get(i), convertTime(temp6.get(i))));
        }

        return allFrTag;
    }

    public boolean isValidated(String user, String pwd) {
        Connection conn = Database.getConnection();
        //since user is pri key, we SELECT password; else, 2 different users
        //might have the same password
        String query = "SELECT password FROM customer WHERE name='"+user+"'";

        String password = "";

        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                password = rs.getString("password");
            }

            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        if(password.equals(pwd))
            return true;
        else
            return false;

    }
    
    //check if this name exists in the database
    public static boolean isExisting(String name) {
        Connection conn = Database.getConnection();
        String query = "SELECT name FROM customer WHERE name = '"+ name + "'";
        String n = "";
        try {
            //execute query
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next())
            {
                //store the values of the respective columns
                //which is determined by column name
                n = rs.getString("name");
            }
            rs.close();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        //if the returned value matches the parameter, return true
        if(n.equals(name))
            return true;
        //else false
        else
            return false;
    }

    //update customers profile picture
    public void setPicture(String newPic) {
        Connection conn = Database.getConnection();
        String query = "UPDATE customer SET picture='"+newPic+"' WHERE name='"
                +this.getName()+"'";
        try {
            //execute query
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();
            stmt.executeUpdate(query);
            conn.commit();
            //Releases this Connection object's database and JDBC resources immediately
            Database.closeDatabase(conn);
        } catch (SQLException ex) {
                ex.printStackTrace();
        }
    }
}
